package ua.kiev.tosik.price.database.dao;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.validator.EmailValidator;
import org.apache.log4j.Logger;
import ua.kiev.tosik.price.database.connectors.ConnectorFactory;
import ua.kiev.tosik.price.database.connectors.IConnector;
import ua.kiev.tosik.price.models.Clients;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ClientsDaoDerby implements IClientsDao {

	private Logger logger = Logger.getLogger("ua.kiev.tosik.price.database.dao");
	private Connection conn;
	private final String colNames;

	public ClientsDaoDerby() {
		logger.debug("Initialize ClientsDao for Derby");
		colNames = "ID, NAME, ADDRESS, PHONE, HOMEPAGE, EMAIL, CREATE_DATE, LAST_UPDATE_DATE, (SELECT MAX(PRICES_DATE) FROM PRICES_FILES WHERE CLIENTS_ID = CLIENTS.ID AND PARSED = 1) AS LAST_PRICE_DATE";
		try {
			IConnector connector = ConnectorFactory.getInstance("Derby");
			conn = connector.getConn();
		} catch (Exception e) {
			logger.fatal(e);
		}
	}

	@Override
	public List<Clients> findAll() {
		List<Clients> clients = new ArrayList<Clients>();
		try {
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery("SELECT " + colNames + " FROM CLIENTS ORDER BY UPPER(NAME)");
			while (rs.next()) {
				clients.add(fillData(rs, new Clients()));
			}
			rs.close();
			st.close();
		} catch (SQLException e) {
			logger.error(e);
		}
		return clients;
	}

	@Override
	public Clients findById(int id) {
		Clients client = null;
		try {
			PreparedStatement pst = conn.prepareStatement("SELECT " + colNames + " FROM CLIENTS WHERE ID = ?");
			pst.setInt(1, id);
			ResultSet rs = pst.executeQuery();
			if ( rs.next() ) {
				client = fillData(rs, new Clients());
			}
			rs.close();
			pst.close();
		} catch (SQLException e) {
			logger.error(e);
		}
		return client;
	}

	@Override
	public List<Clients> findByName(String name) {
		List<Clients> clients = new ArrayList<Clients>();
		try {
			PreparedStatement pst = conn.prepareStatement("SELECT " + colNames + " FROM CLIENTS WHERE UPPER(NAME) LIKE ? ORDER BY UPPER(NAME)");
			pst.setString(1, "%" + name.toUpperCase() + "%");
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				clients.add(fillData(rs, new Clients()));
			}
			rs.close();
			pst.close();
		} catch (SQLException e) {
			logger.error(e);
		}
		return clients;
	}

	@Override
	public Clients save(Clients client) throws Exception {
		if ( client == null ) {
			throw new Exception("Can't save null record");
		}

		if ( !checkRecord(client) ) {
			throw new Exception("Incorrect record or record haven't required fields!");
		}
		logger.debug("Try to create record");
		conn.setAutoCommit(false);
		try {
			PreparedStatement pst = conn.prepareStatement("INSERT INTO CLIENTS (NAME, PHONE, EMAIL, HOMEPAGE, ADDRESS) VALUES(?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
			prepareDataForCreation(pst, client);
			pst.executeUpdate();
			ResultSet rs = pst.getGeneratedKeys();
			if ( rs.next() ) {
				int newId = rs.getInt(1);
				client = findById(newId);
			}
			rs.close();
			pst.close();
			conn.commit();
		} catch (Exception e) {
			logger.error(e);
			conn.rollback();
			throw new Exception(e);
		} finally {
			conn.setAutoCommit(true);
		}
		logger.debug("Record created. ID: " + client.getId());
		return client;
	}

	@Override
	public Clients update(Clients client) throws Exception {
		logger.debug("Try to update record ID: " + client.getId());
		Clients clientsInDb = findById(client.getId());
		if ( clientsInDb == null ) {
			throw new Exception("Record not found!");
		}
		if ( !checkRecord(client) ) {
			throw new Exception("Incorrect record or record haven't required fields!");
		}
		conn.setAutoCommit(false);
		try {
			PreparedStatement pst = conn.prepareStatement("UPDATE CLIENTS SET NAME = ?, PHONE = ?, EMAIL = ?, HOMEPAGE = ?, ADDRESS = ?, LAST_UPDATE_DATE = CURRENT_TIMESTAMP WHERE ID = ?");
			prepareDataForCreation(pst, client);
			pst.setInt(6, client.getId());
			pst.executeUpdate();
			pst.close();
			conn.commit();
			logger.debug("Record updated");
			return findById(client.getId());
		} catch (Exception e) {
			logger.error(e);
			conn.rollback();
			throw new Exception(e);
		} finally {
			conn.setAutoCommit(true);
		}
	}

	@Override
	public Clients delete(Clients client) throws Exception {
		if ( client == null ) {
			logger.error("Can't delete null record");
			throw new Exception("Can't delete null record");
		}
		if ( client.getId() <= 0 ) {
			logger.error("Can't delete null record");
			throw new Exception("No record id for delete");
		}
		logger.debug("Try to delete record ID: " + client.getId());
		conn.setAutoCommit(false);
		try {
			PreparedStatement pst = conn.prepareStatement("DELETE FROM CLIENTS WHERE ID = ?");
			pst.setInt(1, client.getId());
			pst.executeUpdate();
			pst.close();
			conn.commit();
			logger.debug("Record deleted");
			return client;
		} catch (Exception e) {
			logger.error(e);
			conn.rollback();
			throw new Exception(e);
		} finally {
			conn.setAutoCommit(true);
		}
	}

	private Clients fillData(ResultSet rs, Clients client) throws SQLException {
		client.setId(rs.getInt("id"));
		client.setName(rs.getString("name"));
		client.setAddress(rs.getString("address"));
		client.setPhone(rs.getString("phone"));
		client.setHomePage(rs.getString("homepage"));
		client.setEmail(rs.getString("email"));
		client.setCreateDate(rs.getDate("create_date"));
		client.setLastUpdateDate(rs.getDate("last_update_date"));
		client.setLastPriceDate(rs.getDate("last_price_date"));
		return client;
	}

	private void prepareDataForCreation(PreparedStatement ps, Clients client) throws Exception {
		String name = StringUtils.defaultString(client.getName());
		String phone = StringUtils.defaultString(client.getPhone());
		String email = StringUtils.defaultString(client.getEmail());
		String homepage = StringUtils.defaultString(client.getHomePage());
		String address = StringUtils.defaultString(client.getAddress());

		if ( !email.equals("") && !EmailValidator.getInstance().isValid(email) ) {
			logger.error("Incorrect EMail format in [" + email + "]");
			throw new Exception("Incorrect EMail format!");
		}

		if ( name.equals("") ) { ps.setNull(1, Types.VARCHAR); } else { ps.setString(1, name); }
		if ( phone.equals("") ) { ps.setNull(2, Types.VARCHAR); } else { ps.setString(2, phone); }
		if ( email.equals("") ) { ps.setNull(3, Types.VARCHAR); } else { ps.setString(3, email); }
		if ( homepage.equals("") ) { ps.setNull(4, Types.VARCHAR); } else { ps.setString(4, homepage); }
		if ( address.equals("") ) { ps.setNull(5, Types.VARCHAR); } else { ps.setString(5, address); }
	}

	private boolean checkRecord(Clients clients) {
		if ( clients.getName() == null ) {
			return false;
		}
		if ( clients.getName().trim().equals("") ) {
			return false;
		}
		return true;
	}
}
